#include "DBManager.h"
#include <QSqlQuery>
#include <QSqlError>

void DbManager::init(const QString& path) {
    if (QSqlDatabase::contains("main_connection")) {
        m_db = QSqlDatabase::database("main_connection");
    } else {
        m_db = QSqlDatabase::addDatabase("QSQLITE", "main_connection");
        m_db.setDatabaseName(path);
    }

    if (!m_db.open()) {
        qDebug() << "Failed to open database for init:" << m_db.lastError().text();
        return;
    }
    
    QSqlQuery query(m_db);
    
    // 创建用户表
    if (!query.exec("CREATE TABLE IF NOT EXISTS users ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    "username TEXT,"
                    "phone INTEGER,"
                    "password TEXT,"
                    "user_type INTEGER DEFAULT 0)")) {  // 0=工业人员, 1=专家
        qDebug() << "Create users table failed:" << query.lastError().text();
    }
    
    // 创建历史工单表
    if (!query.exec("CREATE TABLE IF NOT EXISTS meetings ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    "room_id INTEGER NOT NULL,"
                    "time DATETIME NOT NULL)")) {
        qDebug() << "Create meetings table failed:" << query.lastError().text();
    }

    // 创建与会人员关联表
    if (!query.exec("CREATE TABLE IF NOT EXISTS participants ("
                    "meeting_id INTEGER,"
                    "user_id INTEGER,"
                    "FOREIGN KEY(meeting_id) REFERENCES meetings(id),"
                    "FOREIGN KEY(user_id) REFERENCES users(id))")) {
        qDebug() << "Create participants table failed:" << query.lastError().text();
    }
    
    // 创建邀请表
    if (!query.exec("CREATE TABLE IF NOT EXISTS invitations ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    "room_id INTEGER NOT NULL,"
                    "host_id INTEGER NOT NULL,"
                    "user_id INTEGER,"
                    "FOREIGN KEY(room_id) REFERENCES rooms(room_id),"
                    "FOREIGN KEY(user_id) REFERENCES users(id),"
                    "FOREIGN KEY(host_id) REFERENCES users(id))")) {
        qDebug() << "Create invitations table failed:" << query.lastError().text();
    }
    
    // 检查是否有测试用户，如果没有则创建一个
    query.prepare("SELECT COUNT(*) FROM users WHERE username = 'test'");
    if (query.exec() && query.next()) {
        int count = query.value(0).toInt();
        if (count == 0) {
            // 创建测试用户（设置为专家）
            query.prepare("INSERT INTO users (username, phone, password, user_type) VALUES ('test', 1234567890, '123456', 1)");
            if (query.exec()) {
                qDebug() << "Test user (expert) created successfully";
            }
        }
    }

    query.prepare("SELECT * FROM users");
    if (query.exec()) {
        while (query.next()) {
            User user;
            user.id = query.value("id").toUInt();
            user.username = query.value("username").toByteArray();
            user.userType = query.value("user_type").toUInt();
            qDebug() << user.toByteArray();
        }
    }

    query.prepare("SELECT * FROM invitations");
    if (query.exec()) {
        while (query.next()) {
            User user;
            qDebug() << query.value("room_id").toUInt() << ',' << query.value("host_id").toUInt() << ',' << query.value("user_id").toUInt();
        }
    }
}

bool DbManager::addInvitation(const quint16& roomId, const quint16& hostId, const quint16& userId) {
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO invitations (room_id, host_id, user_id) VALUES (:room_id, :host_id, :user_id)");
    query.bindValue(":room_id", roomId);
    query.bindValue(":host_id", hostId);
    query.bindValue(":user_id", userId);
    if (!query.exec()) {
        qDebug() << "Add invitation failed:" << query.lastError().text();
        return false;
    }
    return true;
}

void DbManager::delInvitation(const quint16& id) {
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM invitations WHERE id = :id");
    query.bindValue(":id", id);
    if (!query.exec()) {
        qDebug() << "Delete invitation failed:" << query.lastError().text();
    }
}

STATUS DbManager::checkUser(const QString& username, const QString& pass, quint16& outId) {
    QSqlQuery query(m_db);
    query.prepare("SELECT password, id FROM users WHERE username = :username");
    query.bindValue(":username", username);
    if (!query.exec()) {
        qDebug() << "Check user failed";
        return NO_USER;
    }
    if (query.next()) {
        QString storedPass = query.value(0).toString();
        if (storedPass == pass) {
            outId = query.value(1).toUInt();
            return SUCCESS_LOGIN;
        }
        else {
            qDebug() << "Wrong password";
            return WRONG_PASS;
        }
    }
    return ERROR;
}

STATUS DbManager::checkUser(const quint32& phone, const QString& pass, quint16& outId) {
    QSqlQuery query(m_db);
    query.prepare("SELECT password, id FROM users WHERE phone = :phone");
    query.bindValue(":phone", phone);
    if (!query.exec()) {
        qDebug() << "Check phone failed";
        return NO_USER;
    }
    if (query.next()) {
        QString storedPass = query.value(0).toString();
        if (storedPass == pass) {
            outId = query.value(1).toUInt(); 
            return SUCCESS_LOGIN;
        }
        else {
            qDebug() << "Wrong password";
            return WRONG_PASS;
        }
    }
    return ERROR;
}

STATUS DbManager::registerUser(const QString& username, const QString& pass, const quint32& phone, quint16 userType) {
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO users (username, phone, password, user_type) VALUES (:username, :phone, :password, :user_type)");
    query.bindValue(":username", username);
    query.bindValue(":phone", phone);
    query.bindValue(":password", pass);
    query.bindValue(":user_type", userType);
    if (!query.exec()) {
        qDebug() << "Register user failed:" << query.lastError().text();
        return ERROR;
    }
    qDebug() << "Register user with type:" << userType;
    return SUCCESS_REG;
}

//生成房间时生成工单
bool DbManager::addMeeting(const quint16& roomId, const QString& time) {
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO meetings (room_id, time) VALUES (:room_id, :time)");
    query.bindValue(":room_id", roomId);
    query.bindValue(":time", time);
    if (!query.exec()) {
        qDebug() << "Add meeting failed:" << query.lastError().text();
        return false;
    }
    return true;
}

User DbManager::queryUser(const quint16& userId) {
    User user;
    QSqlQuery query(m_db);
    query.prepare("SELECT username, phone, user_type FROM users WHERE id = :id");
    query.bindValue(":id", userId);
    if (!query.exec()) {
        qDebug() << "Query user failed:" << query.lastError().text();
        return user;
    }
    if (query.next()) {
        user.id = userId;
        user.username = query.value(0).toByteArray();
        user.userType = query.value(2).toUInt();
    }
    return user;
}

// 查询用户参与的历史工单
QList<History> DbManager::queryUserHistoryWorkOrders(quint16 userId) {
    QList<History> result;
    
    QSqlQuery query(m_db);
    query.prepare("SELECT m.room_id, m.time FROM meetings m "
                 "INNER JOIN participants p ON m.id = p.meeting_id "
                 "WHERE p.user_id = :user_id "
                 "ORDER BY m.time DESC");
    query.bindValue(":user_id", userId);
    
    if (!query.exec()) {
        qDebug() << "Query user history work orders failed:" << query.lastError().text();
        return result;
    }

    while (query.next()) {
        result.append(History(query.value(0).toUInt(), query.value(1).toByteArray()));
    }
    
    return result;
}

// 查询所有用户（按字母顺序排序）
QList<User> DbManager::queryAllUsersAlphabetical() {
    QList<User> result;
    
    QSqlQuery query(m_db);
    query.prepare("SELECT id, username, phone, user_type FROM users ORDER BY username COLLATE NOCASE ASC");
    
    if (!query.exec()) {
        qDebug() << "Query all users failed:" << query.lastError().text();
        return result;
    }
    
    while (query.next()) {
        User user;
        user.id = query.value(0).toUInt();
        user.username = query.value(1).toByteArray();
        user.userType = query.value(3).toUInt();

        result.append(user);
    }
    
    return result;
}

QList<User> DbManager::queryAllUsersAlphabetical(quint16 userType) {
    QList<User> result;
    
    QSqlQuery query(m_db);
    query.prepare("SELECT id, username, phone FROM users "
                    "WHERE user_type = :user_type "
                    "ORDER BY username COLLATE NOCASE ASC");
    query.bindValue(":user_type", userType);

    if (!query.exec()) {
        qDebug() << "Query all users failed:" << query.lastError().text();
        return result;
    }
    
    while (query.next()) {
        User user;
        user.id = query.value(0).toUInt();
        user.username = query.value(1).toByteArray();
        user.userType = userType;

        result.append(user);
    }
    
    return result;
}

QList<Invitation> DbManager::queryAllInvitations(const quint16& userId) {
    QList<Invitation> result;

    QSqlQuery query(m_db);
    query.prepare("SELECT id, room_id, host_id FROM invitations WHERE user_id = :user_id");
    query.bindValue(":user_id", userId);

    if (!query.exec()) {
        qDebug() << "Query all invitations failed:" << query.lastError().text();
        return result;
    }

    while (query.next()) {
        Invitation invitation;
        invitation.id = query.value(0).toUInt();
        invitation.roomId = query.value(1).toUInt();
        invitation.hostId = query.value(2).toUInt();

        result.append(invitation);
    }

    return result;
}